import re
from collections import Counter
from flask import Flask
from flask_cors import CORS
import pymysql.cursors

app = Flask(__name__)
CORS(app, resources={r"/*": {"origins": "*"}})

db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'root',
    'database': 'db1234',
    'cursorclass': pymysql.cursors.DictCursor
}

def get_db_connection():
    return pymysql.connect(**db_config)

@app.route('/getguwenData')
def getguwenData():
    sql = "SELECT * FROM salary WHERE type = 1"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    return result

@app.route('/getkuaijiData')
def getkuaijiData():
    sql = "SELECT * FROM salary WHERE type = 2"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    return result


@app.route('/getSkillsEcharts')
def getSkillsEcharts():
    sql = "SELECT skill1 FROM salary WHERE type = 1"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    infos = []
    for item in result:
        infos += item['skill1'].replace(' ', '').split('\n')
    infos = Counter(infos).most_common()
    X = [item[0] for item in infos if item[0]]
    Y = [item[1] for item in infos if item[0]]
    return [X[:5], Y[:5]]

@app.route('/getEducationEcharts')
def getEducationEcharts():
    sql = "SELECT education FROM salary WHERE type = 1"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    infos = [item['education'].replace(' ', '').replace('\n', '') for item in result if item['education']]
    infos = Counter(infos).most_common()
    arr = [{'name': item[0], 'value': item[1]} for item in infos]
    return arr
#
@app.route('/geteduAndSalaryEcharts')
def geteduAndSalaryEcharts():
    sql = "SELECT education, salary FROM salary WHERE type = 1"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    print(result)
    total = {}
    totalnum = {}
    for item in result:
        # print(item)
        if item["education"]!=None:
            # edu = item["education"].replace(" ", "").replace("\n", "")
            # price = item["salary"].replace(" ", "").replace("\n", "")
            edu = item["education"]
            price = item["salary"]

            if  (re.findall("薪", price)):
                pattern = r'(\d+)-(\d+)K·(\d+)薪'
                match = re.match(pattern, price)

                if match:
                    start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
                    end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
                    bonus = int(match.group(3))  # 提取第三个捕获组，即 "15"
                    print(start_salary, end_salary, bonus)
                else:
                    print("No match found")
                num1 =  start_salary*1000
                num2 = end_salary * 1000
                avg = (num1 + num2) / 2 * bonus/12
            else:
                pattern = r'(\d+)-(\d+)K'
                match = re.match(pattern, price)
                if match:
                    start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
                    end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
                num1 = start_salary * 1000
                num2 = end_salary * 1000
                avg = (num1 + num2) / 2
            if total.get(edu):
                total[edu] += avg
                totalnum[edu] += 1
            else:
                total[edu] = avg
                totalnum[edu] = 1

    X = []
    Y = []
    for item in total:
        X.append(item)
        Y.append(total[item] / totalnum[item])

    print(X)
    print(Y)
    return {"X": X, "Y": Y}



@app.route('/getkSkillsEcharts')
def getkSkillsEcharts():
    sql = "SELECT skill1 FROM salary WHERE type = 2"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    infos = []
    for item in result:
        infos += item['skill1'].replace(' ', '').split('\n')
    infos = Counter(infos).most_common()
    X = [item[0] for item in infos if item[0]]
    Y = [item[1] for item in infos if item[0]]
    return [X[:5], Y[:5]]

@app.route('/getkEducationEcharts')
def getkEducationEcharts():
    sql = "SELECT education FROM salary WHERE type = 2"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    infos = [item['education'].replace(' ', '').replace('\n', '') for item in result if item['education']]
    infos = Counter(infos).most_common()
    arr = [{'name': item[0], 'value': item[1]} for item in infos]
    return arr

@app.route('/getkeduAndSalaryEcharts')
def getkeduAndSalaryEcharts():
    sql = "SELECT education, salary FROM salary WHERE type = 2"
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    connection.close()
    print(result)
    total = {}
    totalnum = {}
    for item in result:
        # print(item)
        if item["education"]!=None:
            # edu = item["education"].replace(" ", "").replace("\n", "")
            # price = item["salary"].replace(" ", "").replace("\n", "")
            edu = item["education"]
            price = item["salary"]

            if  (re.findall("薪", price)):
                pattern = r'(\d+)-(\d+)K·(\d+)薪'
                match = re.match(pattern, price)

                if match:
                    start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
                    end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
                    bonus = int(match.group(3))  # 提取第三个捕获组，即 "15"
                    print(start_salary, end_salary, bonus)
                else:
                    print("No match found")
                num1 =  start_salary*1000
                num2 = end_salary * 1000
                avg = (num1 + num2) / 2 * bonus/12
            else:
                pattern = r'(\d+)-(\d+)K'
                match = re.match(pattern, price)
                if match:
                    start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
                    end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
                num1 = start_salary * 1000
                num2 = end_salary * 1000
                avg = (num1 + num2) / 2
            if total.get(edu):
                total[edu] += avg
                totalnum[edu] += 1
            else:
                total[edu] = avg
                totalnum[edu] = 1

    X = []
    Y = []
    for item in total:
        X.append(item)
        Y.append(total[item] / totalnum[item])

    print(X)
    print(Y)
    return {"X": X, "Y": Y}


#
# @app.route('/getJavaSkillsEcharts')
# def getJavaSkillsEcharts():
#     sql = "SELECT skill1 FROM sss WHERE type = 3"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     infos = []
#     for item in result:
#         infos += item['skill1'].replace(' ', '').split('\n')
#     infos = Counter(infos).most_common()
#     X = [item[0] for item in infos if item[0]]
#     Y = [item[1] for item in infos if item[0]]
#     return [X[:5], Y[:5]]
#
# @app.route('/getJavaEducationEcharts')
# def getJavaEducationEcharts():
#     sql = "SELECT education FROM sss WHERE type = 3"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     infos = [item['education'].replace(' ', '').replace('\n', '') for item in result if item['education']]
#     infos = Counter(infos).most_common()
#     arr = [{'name': item[0], 'value': item[1]} for item in infos]
#     return arr
#
# @app.route('/getJavaeduAndSalaryEcharts')
# def getJavaeduAndSalaryEcharts():
#     sql = "SELECT education, salary FROM sss WHERE type = 3"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     print(result)
#     total = {}
#     totalnum = {}
#     for item in result:
#         # print(item)
#         if item["education"]!=None:
#             # edu = item["education"].replace(" ", "").replace("\n", "")
#             # price = item["salary"].replace(" ", "").replace("\n", "")
#             edu = item["education"]
#             price = item["salary"]
#
#             if  (re.findall("薪", price)):
#                 pattern = r'(\d+)-(\d+)K·(\d+)薪'
#                 match = re.match(pattern, price)
#
#                 if match:
#                     start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
#                     end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
#                     bonus = int(match.group(3))  # 提取第三个捕获组，即 "15"
#                     print(start_salary, end_salary, bonus)
#                 else:
#                     print("No match found")
#                 num1 =  start_salary*1000
#                 num2 = end_salary * 1000
#                 avg = (num1 + num2) / 2 * bonus/12
#             else:
#                 pattern = r'(\d+)-(\d+)K'
#                 match = re.match(pattern, price)
#                 if match:
#                     start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
#                     end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
#                 num1 = start_salary * 1000
#                 num2 = end_salary * 1000
#                 avg = (num1 + num2) / 2
#             if total.get(edu):
#                 total[edu] += avg
#                 totalnum[edu] += 1
#             else:
#                 total[edu] = avg
#                 totalnum[edu] = 1
#
#     X = []
#     Y = []
#     for item in total:
#         X.append(item)
#         Y.append(total[item] / totalnum[item])
#
#     print(X)
#     print(Y)
#     return {"X": X, "Y": Y}
#
#
# @app.route('/getDataBaseSkillsEcharts')
# def getDataBaseSkillsEcharts():
#     sql = "SELECT skill1 FROM sss WHERE type = 4"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     infos = []
#     for item in result:
#         infos += item['skill1'].replace(' ', '').split('\n')
#     infos = Counter(infos).most_common()
#     X = [item[0] for item in infos if item[0]]
#     Y = [item[1] for item in infos if item[0]]
#     return [X[:5], Y[:5]]
#
# @app.route('/getDataBaseEducationEcharts')
# def getDataBaseEducationEcharts():
#     sql = "SELECT education FROM sss WHERE type = 4"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     infos = [item['education'].replace(' ', '').replace('\n', '') for item in result if item['education']]
#     infos = Counter(infos).most_common()
#     arr = [{'name': item[0], 'value': item[1]} for item in infos]
#     return arr
#
# @app.route('/getDataBaseeduAndSalaryEcharts')
# def getDataBaseeduAndSalaryEcharts():
#     sql = "SELECT education, salary FROM sss WHERE type = 4"
#     connection = get_db_connection()
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     connection.close()
#     print(result)
#     total = {}
#     totalnum = {}
#     for item in result:
#         # print(item)
#         if item["education"]!=None:
#             # edu = item["education"].replace(" ", "").replace("\n", "")
#             # price = item["salary"].replace(" ", "").replace("\n", "")
#             edu = item["education"]
#             price = item["salary"]
#
#             if  (re.findall("薪", price)):
#                 pattern = r'(\d+)-(\d+)K·(\d+)薪'
#                 match = re.match(pattern, price)
#
#                 if match:
#                     start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
#                     end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
#                     bonus = int(match.group(3))  # 提取第三个捕获组，即 "15"
#                     print(start_salary, end_salary, bonus)
#                 else:
#                     print("No match found")
#                 num1 =  start_salary*1000
#                 num2 = end_salary * 1000
#                 avg = (num1 + num2) / 2 * bonus/12
#             else:
#                 pattern = r'(\d+)-(\d+)K'
#                 match = re.match(pattern, price)
#                 if match:
#                     start_salary = int(match.group(1))  # 提取第一个捕获组，即 "18"
#                     end_salary = int(match.group(2))  # 提取第二个捕获组，即 "30"
#                 num1 = start_salary * 1000
#                 num2 = end_salary * 1000
#                 avg = (num1 + num2) / 2
#             if total.get(edu):
#                 total[edu] += avg
#                 totalnum[edu] += 1
#             else:
#                 total[edu] = avg
#                 totalnum[edu] = 1
#
#     X = []
#     Y = []
#     for item in total:
#         X.append(item)
#         Y.append(total[item] / totalnum[item])
#
#     print(X)
#     print(Y)
#     return {"X": X, "Y": Y}


if __name__ == '__main__':
    app.run()

